package User;

import javax.naming.InitialContext;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class UserInfo {
    private String username;
    private String password;
    private String surname;
    private String name;

    public UserInfo(String username, String password, String surname, String name, String mail, String img) {
        this.username = username;
        this.password = password;
        this.surname = surname;
        this.name = name;
        this.mail = mail;
        if(img==null)this.img = "css/a.jpg";
        else this.img=img;
    }

    private String mail;
    private String img;

    public UserInfo(){

        try{
            InitialContext context=new InitialContext() ;
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn=null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `Dgut` (\n" +
                    "      `username` char(12) NOT NULL UNIQUE,\n" +
                    "      `surname` varchar(20) NOT NULL,\n"+
                    "      `name` varchar(20) NOT NULL,\n" +
                    "      `group` varchar(20) NOT NULL,\n" +
                    "      `faculty_title` varchar(30) NOT NULL,\n" +
                    "       PRIMARY KEY (`username`)\n" +
                    "    );");
            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `Local` (\n" +
                    "      `username` char(30) NOT NULL UNIQUE,\n" +
                    "      `password` varchar(30) NOT NULL,\n" +
                    "      `surname` varchar(11) NOT NULL,\n" +
                    "      `name` varchar(11) NOT NULL,\n" +
                    "      `mail` char(30),\n"+
                    "      `img` varchar(30) NOT NULL DEFAULT 'css/a.jpg',\n" +
                    "      `dgut_username` char(12),\n" +
                    "       PRIMARY KEY (`username`),\n" +
                    "      FOREIGN KEY (`dgut_username`) REFERENCES `Dgut`(`username`)\n" +
                    "    );");
            stmt.close();
            conn.close();
            context.close();
        }catch(Exception e){
            e.printStackTrace();
        }

    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMail() {
        return mail;
    }

    public void setMail(String mail) {
        this.mail = mail;
    }
    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }

    public boolean CheckLogin(String username,String password) throws Exception//检查登录的账号密码是否正确
    {
        boolean login=false;
        try {
            InitialContext context=new InitialContext() ;
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn=null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from LOCAL where `username`='"+username+"' AND `password`='"+password+"';");
            login=rs.next();
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return login;
    }
    public boolean CheckReigster(String username)//检查用户名是否已被注册
    {
        boolean reigster=false;
        try {
            InitialContext context=new InitialContext() ;
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn=null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from LOCAL `username`='"+username+"';");
            reigster=rs.next();
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e){}
        return reigster;
    }
    public boolean CheckBind1(String d)//检查莞工账号是否已经被绑定
    {
       boolean bind1=false;
       try {
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn = null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs=stmt.executeQuery("select * from LOCAL where `dgut_username`='"+d+"';");
            bind1=rs.next();
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return bind1;
    }
    public String CheckBind2(String u)//检查本地账号是否已经绑定莞工账号
    {
        String bind2=null;
        try {
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn = null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs=stmt.executeQuery("select * from LOCAL where `username`='"+u+"';");
            if(rs.next())bind2=rs.getString("dgut_username");
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return bind2;
    }
    public void SaveDgut(Userjson user)//将莞工账号的信息保存到数据库
    {
        try {
            InitialContext context=new InitialContext() ;
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn=null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            String surname = user.getName().substring(0, 1);
            String newname = user.getName().substring(1, user.getName().length());
            String username=user.getUsername();
            ResultSet rs = stmt.executeQuery("select * from DGUT where `username`='"+username+"';");
            if(!rs.next()) {
                stmt.executeUpdate("INSERT INTO `Dgut` (`username`,`surname`,`name`,`group`,`faculty_title`) " +
                        "VALUES ('" + user.getUsername() + "','" + surname + "','" + newname + "','" + user.getGroup() + "','" + user.getFaculty_title() + "');");
            }
            stmt.close();
            conn.close();
            context.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    public void SaveLocal(UserInfo user)//将本地账号的信息保存到数据库
    {
        try {
            InitialContext context=new InitialContext() ;
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn=null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs=stmt.executeQuery("select * from LOCAL where `username`='"+user.getUsername()+"';");
            if(!rs.next()) {
                stmt.executeUpdate("INSERT INTO `Local` (`username`,`password`,`surname`,`name`,`mail`,`img`) " +
                        "VALUES ('" + user.getUsername() + "','" + user.getPassword() + "','" + user.getSurname() + "','" + user.getName() + "','" + user.getMail() + "','" + user.getImg() + "');");
            }
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public UserInfo Login(String u,String p)
    {
        UserInfo us=new UserInfo();
        try {
            InitialContext context=new InitialContext() ;
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn=null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from LOCAL where `username`='"+u+"' and `password`='"+p+"';");
            if(rs.next()) {
                if(rs.getString("username").equals(u)&&rs.getString("password").equals(p)){
                    us=new UserInfo(u,p,rs.getString("surname"),rs.getString("name"),rs.getString("mail"),rs.getString("img"));
                };
            }
            stmt.close();
            conn.close();
            context.close();
            return us;
        }catch (Exception e){
            e.printStackTrace();
        }
        return us;
    }
    public String GetTX(String user)//从数据库中获取头像
    {
        String img=null;
        try {
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn = null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from LOCAL where `username`='"+user+"';");
            if(rs.next()){
                img=rs.getString("img");
            }
            stmt.close();
            conn.close();
            context.close();

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return img;
    }
    public void SetTX(String user,String img)//修改头像
    {
        try {
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn = null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate("update LOCAL set `img`='"+img+"' where `username` ='"+user+"';");
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    public void Bind(String u,String d)//将莞工账号与本地账号进行绑定
    {
        try{
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("jpa/DSTest");
            Connection conn = null;
            Statement stmt = null;
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate("update LOCAL set `dgut_username`='"+d+"' where `username` ='"+u+"';");
            stmt.close();
            conn.close();
            context.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}